Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: 2 Criteria Lookup Without #N/A

1. I agreed to these rules
Join Date
31st March 2008
Posts
5

## 2 Criteria Lookup Without #N/A

I am trying to lookup the value in a table based on two criteria. For the purpose of illustration, I attached the file here.
What I am trying to do is that I want to fill Table 2 with data from Table 1 which match the company and the dates. If all the dates match for the companies, that problem would be much easier, However, as you can see the dates from each company do not match on the same row,totally massed things up.

In my real file I have 70 companies, any one have a solution for this ???It would be really appreciated for your help.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
21st February 2006
Location
London, UK
Posts
3,867

## Re: Find Value And Return Row Number

Are you asking for a way to hide or remove the N/A error?

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Find Value And Return Row Number

Originally Posted by StephenR
Are you asking for a way to hide or remove the N/A error?
If so, try,

B17 and copied down & across,

=IF(ISNUMBER(MATCH(B\$16,INDEX(\$A\$2:\$F\$13,,ROWS(\$B\$17:\$B17)*2-2+1),0)),HLOOKUP(\$A17,\$A\$1:\$F\$13,MATCH(B\$16,INDEX(\$A\$2:\$F\$13,,ROWS(\$B\$17:\$B17)*2-2+1),0)+1,0),"")

HTH

4. I agreed to these rules
Join Date
31st March 2008
Posts
5

## Re: Find Value And Return Row Number

not exactly. Sorry for unclear statement of my question.

The attachted file is justs a simple example of my database, In my real database I have 70 companies, and the data will be updated from time to time from Bloomberg.

So I am looking for other way to retrive the right data, rather than what I did in the file by selecting the colums one-by-one in the Match() function for each company to find the row number.

But also thank you to tell me the way to hide the error message, useful too!

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Find Value And Return Row Number

rather than what I did in the file by selecting the colums one-by-one in the Match() function for each company to find the row number.
If each company has two column data then try my formula. You don't need to select each column for each company.

6. I agreed to these rules
Join Date
31st March 2008
Posts
5

## Re: Find Value And Return Row Number

thanks for your quick reply Kris, I have applied your formula in Cell B 17, no value appears at all.can you work on the file and upload to the forum please?Don't know what went wrong.

Thanks

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Find Value And Return Row Number

I have been using Krish's formula and it works very nicely, see attached.

8. I agreed to these rules
Join Date
31st March 2008
Posts
5

## Re: Find Value And Return Row Number

Indeed, it works!!!!!!!!!!

Sorry, earlier I was at work, we use European standard format at work, so the formula didn't work.

Thanks a lot Kris.
I am gonna try apply it to my data sheet.

Excel Video Tutorials / Excel Dashboards Reports

9. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Find Value And Return Row Number

Originally Posted by linoutandabout
Indeed, it works!!!!!!!!!!

Sorry, earlier I was at work, we use European standard format at work, so the formula didn't work.

Thanks a lot Kris.
I am gonna try apply it to my data sheet.

You are welcome!!

10. I agreed to these rules
Join Date
31st March 2008
Posts
5

## Re: 2 Criteria Lookup Without #N/A

It totally works in my database, you made my day Kris.

zillions of thanks :D

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno